package kt;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import kt.dal.DAL;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

/**
 * Example class to submit a message to a SMSC using synchronous communication.
 * This class simply binds to the server, submits a message, and then unbinds.
 * 
 * @see ie.omk.smpp.examples.ParseArgs ParseArgs for details on running this
 *      class.
 */
public class DailyReportJob implements Job {
	//private Log logger = LogFactory.getLog(SendMTJob.class);
    private static final Logger logger = LogManager.getLogger(DailyReportJob.class);
   
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		logger.info("start executing the DailyReportJob");		
		java.sql.Connection conn = null;
		String strSQL = "select SUM(CEILING(billsec/60)) from cdr where DATE(calldate) = DATE( ADDDATE(now(),INTERVAL -1 day))";
		String strSQLMonth = "select SUM(CEILING(billsec/60)) from cdr where Month(calldate) = Month( ADDDATE(now(),INTERVAL -1 day))";
		try {
			// logger.info("get MT of " + operator);
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/cdr?autoReconnect=true",
					"root", "123456");
			int re = 0;
			int reMonth = 0;	
			PreparedStatement preStmt = conn.prepareStatement(strSQL);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re += rs.getInt(1);
			}
			rs.close();
			preStmt.close();
			
			preStmt = conn.prepareStatement(strSQLMonth);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				reMonth += rs.getInt(1);
			}
			
			Date now = new Date(System.currentTimeMillis());
			DAL.saveMT_Metfone("855889723456", "revenue " + now.getDate() + ": " + re/10 
					+ "USD; total this month: " + reMonth/10, "6155");
			DAL.saveMT_Metfone("855979066460", "revenue " + now.getDate() + ": " + re/10 
					+ "USD; total this month: " + reMonth/10, "6155");
			
			rs.close();
			preStmt.close();
			
		} catch (Exception e) {
			logger.error("Error executing SQL " + strSQL, e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					logger.error("error when close the conn", e);
				}
			}
		}
		logger.info("end executing the DailyReportJob");
	}
}

